package prototech.ahedra.br;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import prototech.ahedra.model.DbCon;

public class ProcessUpload {
	private static Connection con;

	public static void ProcessFile(String file) throws IOException,
			SQLException {
		ResultSet rs = null;

		try {
			InputStream myxls = new FileInputStream(file);
			HSSFWorkbook wb = new HSSFWorkbook(myxls);
			HSSFSheet sheet = wb.getSheetAt(0);
			HSSFRow row = sheet.getRow(0);

			int renglon = 0;
			int rowcount = 0;
			while (row != null) {
				HSSFCell cell = row.getCell(0);

				String cve = cell.getStringCellValue();
				String valido = "incorrecta";

				con = DbCon.getConnection();

				if (!con.isClosed()) {
					// Insert value into SQL Table
					String qry = "SELECT * FROM padron WHERE patidx=? and matidx=? and nomidx=? and anoidx=?";

					PreparedStatement ps = con.prepareStatement(qry);

					ps.setString(1, cve.substring(0, 2));
					ps.setString(2, cve.substring(2, 4));
					ps.setString(3, cve.substring(4, 6));
					ps.setString(4, cve.substring(6, 8));

					rs = ps.executeQuery();

					try {
						while (rs.next()) {
							if (cve.endsWith(rs.getString("clave"))) {
								valido = "correcta";
								break;
							}
						}
					} catch (Exception ex) {
						System.out.print("Entro al catch del next");
						valido="incorrecta";
					}
					System.out.print("La clave " + cve
							+ " es " + valido + "\n");
				} else {
					throw new SQLException("Sin conexión a la base de datos");
				}
				renglon++;
				row = sheet.getRow(renglon);
			}
		} catch (FileNotFoundException ex) {
			throw ex;
		} finally {
			if (!con.isClosed()) {
				con.close();
			}
		}
	}
}
